using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VQPROBVS
    {
        public DataRow Convert2QPROBVS(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryQPROBVS(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["QPVMANDANT"] = " ".PadRight(2 ); 
         dwReturn["QPVGKREGEL"] = " ".PadRight(8 ); 
         dwReturn["QPVPRVORS"] = " ".PadRight(8 ); 
         dwReturn["QPVAENZL"] = " ".PadRight(0 ); 
         dwReturn["QPVAENB"] = " ".PadRight(8 ); 
         dwReturn["QPVAEND"] = " ".PadRight(0 ); 
         dwReturn["QPVNEUB"] = " ".PadRight(8 ); 
         dwReturn["QPVNEUD"] = " ".PadRight(0 ); 
         dwReturn["QPVPRART"] = " ".PadRight(8 ); 
         dwReturn["QPVPRLG"] = " ".PadRight(0 ); 
         dwReturn["QPVPRIO1"] = " ".PadRight(1 ); 
         dwReturn["QPVPRMTR"] = " ".PadRight(0 ); 
         dwReturn["QPVPR1MTR"] = " ".PadRight(0 ); 
         dwReturn["QPVGSMETRGENDE"] = " ".PadRight(1 ); 
         dwReturn["QPVPRIO2"] = " ".PadRight(1 ); 
         dwReturn["QPVPRSHADW"] = " ".PadRight(0 ); 
         dwReturn["QPVPR1SHADW"] = " ".PadRight(0 ); 
         dwReturn["QPVPRIO3"] = " ".PadRight(1 ); 
         dwReturn["QPVPRPROZ"] = " ".PadRight(0 ); 
         dwReturn["QPVPR1PROZ"] = " ".PadRight(0 ); 
         dwReturn["QPVPRIO4"] = " ".PadRight(1 ); 
         dwReturn["QPVPRSTK"] = " ".PadRight(0 ); 
         dwReturn["QPVPR1STK"] = " ".PadRight(0 ); 
         dwReturn["QPVSTKANF"] = " ".PadRight(1 ); 
         dwReturn["QPVSTKENDE"] = " ".PadRight(1 ); 
         dwReturn["QPVPRIO5"] = " ".PadRight(1 ); 
         dwReturn["QPVPRURSTK"] = " ".PadRight(0 ); 
         dwReturn["QPVPR1URSTK"] = " ".PadRight(0 ); 
         dwReturn["QPVURSTKANF"] = " ".PadRight(1 ); 
         dwReturn["QPVURSTKENDE"] = " ".PadRight(1 ); 
         dwReturn["QPVFILLER"] = " ".PadRight(30 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapQPROBVS(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQPROBVS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryQPROBVS(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapQPROBVS(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertQPROBVS(dwLocal, ITXDBVERSION);
            }
            UpdateQPROBVS(dwLocal);
        }
        public void InsertQPROBVS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO QPROBVS(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryQPROBVS(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM QPROBVS WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM QPROBVS  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "QPROBVS";
            Return = Result;
            return Return;
        }
        public void DeleteQPROBVS(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QPROBVS   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateQPROBVS(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE QPROBVS SET "
                    +"QPVMANDANT= :QPVMANDANT,"
                    +"QPVGKREGEL= :QPVGKREGEL,"
                    +"QPVPRVORS= :QPVPRVORS,"
                    +"QPVAENZL= :QPVAENZL,"
                    +"QPVAENB= :QPVAENB,"
                    +"QPVAEND= :QPVAEND,"
                    +"QPVNEUB= :QPVNEUB,"
                    +"QPVNEUD= :QPVNEUD,"
                    +"QPVPRART= :QPVPRART,"
                    +"QPVPRLG= :QPVPRLG,"
                    +"QPVPRIO1= :QPVPRIO1,"
                    +"QPVPRMTR= :QPVPRMTR,"
                    +"QPVPR1MTR= :QPVPR1MTR,"
                    +"QPVGSMETRGENDE= :QPVGSMETRGENDE,"
                    +"QPVPRIO2= :QPVPRIO2,"
                    +"QPVPRSHADW= :QPVPRSHADW,"
                    +"QPVPR1SHADW= :QPVPR1SHADW,"
                    +"QPVPRIO3= :QPVPRIO3,"
                    +"QPVPRPROZ= :QPVPRPROZ,"
                    +"QPVPR1PROZ= :QPVPR1PROZ,"
                    +"QPVPRIO4= :QPVPRIO4,"
                    +"QPVPRSTK= :QPVPRSTK,"
                    +"QPVPR1STK= :QPVPR1STK,"
                    +"QPVSTKANF= :QPVSTKANF,"
                    +"QPVSTKENDE= :QPVSTKENDE,"
                    +"QPVPRIO5= :QPVPRIO5,"
                    +"QPVPRURSTK= :QPVPRURSTK,"
                    +"QPVPR1URSTK= :QPVPR1URSTK,"
                    +"QPVURSTKANF= :QPVURSTKANF,"
                    +"QPVURSTKENDE= :QPVURSTKENDE,"
                    +"QPVFILLER= :QPVFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("QPVMANDANT", OracleType.NVarChar).Value = dwData["QPVMANDANT"].ToString(); 
          or1.Parameters.Add("QPVGKREGEL", OracleType.NVarChar).Value = dwData["QPVGKREGEL"].ToString(); 
          or1.Parameters.Add("QPVPRVORS", OracleType.NVarChar).Value = dwData["QPVPRVORS"].ToString(); 
          or1.Parameters.Add("QPVAENZL", OracleType.NVarChar).Value = dwData["QPVAENZL"].ToString(); 
          or1.Parameters.Add("QPVAENB", OracleType.NVarChar).Value = dwData["QPVAENB"].ToString(); 
          or1.Parameters.Add("QPVAEND", OracleType.NVarChar).Value = dwData["QPVAEND"].ToString(); 
          or1.Parameters.Add("QPVNEUB", OracleType.NVarChar).Value = dwData["QPVNEUB"].ToString(); 
          or1.Parameters.Add("QPVNEUD", OracleType.NVarChar).Value = dwData["QPVNEUD"].ToString(); 
          or1.Parameters.Add("QPVPRART", OracleType.NVarChar).Value = dwData["QPVPRART"].ToString(); 
          or1.Parameters.Add("QPVPRLG", OracleType.NVarChar).Value = dwData["QPVPRLG"].ToString(); 
          or1.Parameters.Add("QPVPRIO1", OracleType.NVarChar).Value = dwData["QPVPRIO1"].ToString(); 
          or1.Parameters.Add("QPVPRMTR", OracleType.NVarChar).Value = dwData["QPVPRMTR"].ToString(); 
          or1.Parameters.Add("QPVPR1MTR", OracleType.NVarChar).Value = dwData["QPVPR1MTR"].ToString(); 
          or1.Parameters.Add("QPVGSMETRGENDE", OracleType.NVarChar).Value = dwData["QPVGSMETRGENDE"].ToString(); 
          or1.Parameters.Add("QPVPRIO2", OracleType.NVarChar).Value = dwData["QPVPRIO2"].ToString(); 
          or1.Parameters.Add("QPVPRSHADW", OracleType.NVarChar).Value = dwData["QPVPRSHADW"].ToString(); 
          or1.Parameters.Add("QPVPR1SHADW", OracleType.NVarChar).Value = dwData["QPVPR1SHADW"].ToString(); 
          or1.Parameters.Add("QPVPRIO3", OracleType.NVarChar).Value = dwData["QPVPRIO3"].ToString(); 
          or1.Parameters.Add("QPVPRPROZ", OracleType.NVarChar).Value = dwData["QPVPRPROZ"].ToString(); 
          or1.Parameters.Add("QPVPR1PROZ", OracleType.NVarChar).Value = dwData["QPVPR1PROZ"].ToString(); 
          or1.Parameters.Add("QPVPRIO4", OracleType.NVarChar).Value = dwData["QPVPRIO4"].ToString(); 
          or1.Parameters.Add("QPVPRSTK", OracleType.NVarChar).Value = dwData["QPVPRSTK"].ToString(); 
          or1.Parameters.Add("QPVPR1STK", OracleType.NVarChar).Value = dwData["QPVPR1STK"].ToString(); 
          or1.Parameters.Add("QPVSTKANF", OracleType.NVarChar).Value = dwData["QPVSTKANF"].ToString(); 
          or1.Parameters.Add("QPVSTKENDE", OracleType.NVarChar).Value = dwData["QPVSTKENDE"].ToString(); 
          or1.Parameters.Add("QPVPRIO5", OracleType.NVarChar).Value = dwData["QPVPRIO5"].ToString(); 
          or1.Parameters.Add("QPVPRURSTK", OracleType.NVarChar).Value = dwData["QPVPRURSTK"].ToString(); 
          or1.Parameters.Add("QPVPR1URSTK", OracleType.NVarChar).Value = dwData["QPVPR1URSTK"].ToString(); 
          or1.Parameters.Add("QPVURSTKANF", OracleType.NVarChar).Value = dwData["QPVURSTKANF"].ToString(); 
          or1.Parameters.Add("QPVURSTKENDE", OracleType.NVarChar).Value = dwData["QPVURSTKENDE"].ToString(); 
          or1.Parameters.Add("QPVFILLER", OracleType.NVarChar).Value = dwData["QPVFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
